Banner Banner Banner Banner Banner Banner Banner Banner Banner

Oracle Database 11g: Analytic

Oracle Database 11g: Analytic SQL for Data Warehousing Content Details

Introduction

  • Course Objectives
  • Course Agenda
  • Class Accounts Information
  • Appendices Used in this Course
  • Sample Schemas Used in this Course
  • SQL Environments Available in the Course
  • Overview of Oracle SQL Developer
  • Oracle 11g SQL and Data Warehousing Documentation and Additional Resources

Grouping and Aggregating Data Using SQL

  • What is Analytic SQL?
  • Analytic SQL in Data Warehouses Agenda: SQL for Aggregation
  • SQL for Analysis and Reporting, and SQL for Modeling
  • Generating Reports by Grouping Related Data
  • Using the GROUP BY Clause With the ROLLUP and CUBE Operators
  • Using the ROLLUP and CUBE Operators
  • Using the GROUPING Function
  • Working With GROUPING SETS
  • Working With Composite Columns and Concatenated Groupings

Analyzing and Reporting Data Using SQL

  • Overview of SQL for Analysis and Reporting Functions
  • Identifying the SQL Ranking Functions
  • Controlling the Ranking Order
  • Ranking on Multiple Expressions
  • Using the RANK, DENSE_RANK, and PERCENT_RANK Functions
  • Ranking Per CUBE and ROLLUP
  • Using the LAG/LEAD Functions
  • Performing Pivoting Operations Using the PIVOT and UNPIVOT Clauses

Modeling Data Using SQL

  • Overview of SQL for Modeling Data
  • Integrating Inter-row Calculations in SQL
  • Working With the SQL MODEL Clause
  • Cell and Range References
  • Using the CV()Function
  • Using the FOR Construct with IN List Operator, Incremental Values, and a Sub query
  • Using Reference Models
  • Cyclic Rules in Models

Hierarchical Retrieval

  • Hierarchical Retrieval: Overview
  • Natural Tree Structure
  • Hierarchical Queries
  • Walking the Tree
  • Walking the Tree: From the Bottom Up and From the Top Down
  • Ranking Rows with the LEVEL Pseudo column
  • Formatting Hierarchical Reports Using LEVEL and LPAD
  • Pruning Branches and Nodes

Analyzing Data Using Regular Expressions

  • The Benefits of Using Regular Expressions
  • Using the Regular Expressions Functions and Conditions in SQL
  • Using Met a characters with Regular Expressions
  • Performing a Basic Search Using the REGEXP_LIKE Condition
  • Finding Patterns Using the REGEXP_INSTR Function
  • Extracting Substrings Using the REGEXP_SUBSTR Function
  • Replacing Patterns Using the REGEXP_REPLACE Function
  • Using Sub expressions with Regular Expression Support